﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using schedulelist.ORM.dao.interfaces;
using schedulelist.ORM.domain;
using NHibernate;
namespace schedulelist.ORM.dao.classes
{
    class TicketsDAO : GenericDAO<Tickets>, ITicketsDAO
    {
        public TicketsDAO(ISession session) : base(session) { }

        public Tickets getTicketByOwner(
        User owner)
        {
            var list = session.CreateSQLQuery(
            "SELECT tickets.* FROM tickets" +
            " WHERE tickets.ownerId='" + owner.Id + "'")
            .AddEntity("Tickets", typeof(Tickets))
            .List<Tickets>();
            Tickets t = list[0];
            return t;
        }

        public Tickets getTicketById( int id)
        {
            var list = session.CreateSQLQuery(
            "SELECT tickets.* FROM tickets" +
            " WHERE tickets.Id='" + id + "'")
            .AddEntity("Tickets", typeof(Tickets))
            .List<Tickets>();
            Tickets t = list[0];
            return t;
        }

        public IList<Tickets> getAllTicketsSortedByDM()
        {
            var list = session.CreateSQLQuery(
            "SELECT tickets.* FROM tickets" +
            " ORDER BY tickets.dm")
            .AddEntity("Tickets", typeof(Tickets))
            .List<Tickets>();
            return list;
        }

        public IList<Tickets> getTicketsByCat(Category cat)
        {
            var list = session.CreateSQLQuery(
            "SELECT tickets.* FROM tickets " +
            "WHERE tickets.catId ="+cat.Id+
            " ORDER BY tickets.dm")
            .AddEntity("Tickets", typeof(Tickets))
            .List<Tickets>();
            return list;
        }
        public IList<Tickets> getTicketsByCust(Customer cust)
        {
            var list = session.CreateSQLQuery(
            "SELECT tickets.* FROM tickets " +
            "WHERE tickets.custId =" + cust.Id +
            " ORDER BY tickets.dm")
            .AddEntity("Tickets", typeof(Tickets))
            .List<Tickets>();
            return list;
        }

        public IList<Tickets> getTicketsByOwner(User u)
        {
            var list = session.CreateSQLQuery(
            "SELECT tickets.* FROM tickets " +
            "WHERE tickets.ownerId =" + u.Id +
            " ORDER BY tickets.dm")
            .AddEntity("Tickets", typeof(Tickets))
            .List<Tickets>();
            return list;
        }
        public IList<Tickets> getTicketsByExecutor(User ex)
        {
            var list = session.CreateSQLQuery(
            "SELECT tickets.* FROM tickets " +
            "WHERE tickets.execId =" + ex.Id +
            " ORDER BY tickets.dm")
            .AddEntity("Tickets", typeof(Tickets))
            .List<Tickets>();
            return list;
        }

    }
}
